"""
Case Type   : mysql protocol compatibility
Case Name   : 协议兼容下验证用户权限的grant和revoke
Create At   : 2024/10/23
Owner       : lvlintao666
Description :
    1、设置协议兼容相关配置
    2、建表
    3、插入数据
    4、新建用户、授权
    5、新建用户插入数据
    6、回收权限
    7、新建用户插入数据
    8、清理环境
Expect      :
    1、配置成功
    2、建表成功
    3、插入成功
    4、新建成功
    5、插入成功
    6、回收成功
    7、插入失败
    8、清理成功
History     :
"""

import os
import time
import unittest

from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.CommonMySQL import CommonMySQL
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class ProtoTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.com = Common()
        self.comsh = CommonSH('PrimaryDbUser')
        self.pri_node = Node('PrimaryDbUser')
        self.my_node = Node('mysql5')
        self.db_name = "proto_test_db"
        self.conf_path = os.path.join(macro.DB_INSTANCE_PATH,
                                      'postgresql.conf')
        self.create_db = f"drop database if exists {self.db_name};" \
                         f"create database {self.db_name} dbcompatibility='B'"
        self.user = f"proto_test_39"
        self.password = f"proto_test123"
        self.create_u = f"drop user if exists {self.user} cascade;" \
                        f"create user {self.user} with password '{self.password}';" \
                        f"select set_native_password('{self.user}','{self.password}','');" \
                        f"grant all privileges to {self.user};"
        self.show_cmd = f"show enable_dolphin_proto;" \
                        f"show dolphin_server_port;"
        self.c_tb = f"drop table if exists public.tab_protocol_0039;" \
                    f"create table public.tab_protocol_0039( id tinyint," \
                    f"name varchar(100), score tinyint);"
        self.c_user = f"drop user if exists u_protocol_0039; " \
                      f"create user if not exists u_protocol_0039 identified by 'Test@123';" \
                      f"select set_native_password('u_protocol_0039','Test@123','');"
        self.grant = f"grant select,insert on public.tab_protocol_0039 to u_protocol_0039;"
        self.insert1 = f"""
                      insert into public.tab_protocol_0039(id,name,score) 
                      values(1,'zs',81),(2,'ls',89),(1,'ww',67);
                      """
        self.insert2 = f"insert into public.tab_protocol_0039(id,name,score) values(4,'zl',77);"
        self.insert3 = f"insert into public.tab_protocol_0039(id,name,score) values(5,'nl',73); "
        self.update = f"update public.tab_protocol_0039 set score = 99 where name = 'ww'; "
        self.revoke = f"revoke insert on public.tab_protocol_0039 from u_protocol_0039;"
        self.clean_tb = f"drop table if exists public.tab_protocol_0039 cascade constraints; " \
                        f"drop user if exists u_protocol_0039 cascade;" \
                        f"drop user if exists {self.user} cascade;"
        self.clean_db = f"drop database {self.db_name};"

    def test_index(self):
        text = '--step1:设置协议兼容相关配置;expect:创建成功--'
        logger.info(text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       'enable_dolphin_proto = on')
        logger.info(msg)
        my_port = int(self.pri_node.db_port) + 10
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       f'dolphin_server_port = {my_port}')
        logger.info(msg)
        sed_cmd = f"sed -i '$a dolphin.default_database_name={self.db_name}' " \
                  f"{self.conf_path}"
        logger.info(sed_cmd)
        res = self.com.get_sh_result(self.pri_node, sed_cmd)
        logger.info(res)

        msg = self.comsh.restart_db_cluster()
        logger.info(msg)
        show_res = self.comsh.execut_db_sql(self.show_cmd)
        logger.info(show_res)
        self.assertIn('on', show_res, '执行失败' + text)
        self.assertIn(f'{my_port}', show_res, '执行失败' + text)

        logger.info(self.create_db)
        db_res = self.comsh.execut_db_sql(self.create_db)
        logger.info(db_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, db_res,
                      '执行失败' + text)
        logger.info(self.create_u)
        u_res = self.comsh.execut_db_sql(self.create_u, dbname=self.db_name)
        logger.info(u_res)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, u_res,
                      '执行失败' + text)

        text = '--step2:M*连接数据库建表;expect:创建成功--'
        logger.info(text)
        my_sh = CommonMySQL('mysql5')
        c_res = my_sh.execut_db_sql(sql=self.c_tb, dbname=self.db_name, host=self.pri_node.db_host,
                                    port=my_port, user=self.user, password=self.password)
        logger.info(c_res)
        self.assertNotIn('ERROR', c_res, '执行失败' + text)

        text = '--step3:M*连接数据库插入数据;expect:创建成功--'
        logger.info(text)
        insert_res = my_sh.execut_db_sql(sql=self.insert1, dbname=self.db_name, host=self.pri_node.db_host,
                                         port=my_port, user=self.user, password=self.password)
        logger.info(insert_res)
        self.assertNotIn('ERROR', insert_res, '执行失败' + text)

        text = '--step4:M*连接数据库新建用户授权;expect:创建成功--'
        logger.info(text)
        c_res = my_sh.execut_db_sql(sql=self.c_user, dbname=self.db_name, host=self.pri_node.db_host,
                                    port=my_port, user=self.user, password=self.password)
        logger.info(c_res)
        self.assertNotIn('ERROR', c_res, '执行失败' + text)
        g_res = my_sh.execut_db_sql(sql=self.grant, dbname=self.db_name, host=self.pri_node.db_host,
                                    port=my_port, user=self.user, password=self.password)
        logger.info(g_res)
        self.assertNotIn('ERROR', c_res, '执行失败' + text)

        text = '--step5:M*连接数据库新建用户insert数据;expect:成功--'
        logger.info(text)
        res = my_sh.execut_db_sql(sql=self.insert2, dbname=self.db_name, host=self.pri_node.db_host,
                                  port=my_port, user='u_protocol_0039', password='Test@123')
        logger.info(res)
        self.assertNotIn('ERROR', res, '执行失败' + text)

        res = my_sh.execut_db_sql(sql=self.update, dbname=self.db_name, host=self.pri_node.db_host,
                                  port=my_port, user=self.user, password=self.password)
        logger.info(res)
        self.assertNotIn('ERROR', res, '执行失败' + text)

        text = '--step6:M*连接数据库对新建用户回收权限;expect:成功--'
        logger.info(text)
        res = my_sh.execut_db_sql(sql=self.revoke, dbname=self.db_name, host=self.pri_node.db_host,
                                  port=my_port, user=self.user, password=self.password)
        logger.info(res)
        self.assertNotIn('ERROR', res, '执行失败' + text)

        text = '--step7:M*连接数据库新建用户插入数据;expect:成功--'
        logger.info(text)
        res = my_sh.execut_db_sql(sql=self.insert3, dbname=self.db_name, host=self.pri_node.db_host,
                                  port=my_port, user='u_protocol_0039', password='Test@123')
        logger.info(res)
        self.assertIn('permission denied', res, '执行失败' + text)

    def tearDown(self):
        text = '--step8:清理环境;expect:成功--'
        logger.info(text)
        clean_res = self.comsh.execut_db_sql(self.clean_tb, dbname=self.db_name)
        logger.info(clean_res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, clean_res,
                      '执行失败' + text)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, clean_res,
                      '执行失败' + text)
        clean_res = self.comsh.execut_db_sql(self.clean_db)
        logger.info(clean_res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, clean_res,
                      '执行失败' + text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       'enable_dolphin_proto = off')
        logger.info(msg)
        self.assertTrue(msg, '执行失败' + text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       f'dolphin_server_port = 3308')
        logger.info(msg)
        self.assertTrue(msg, '执行失败' + text)
        sed_cmd = f"sed -i '/dolphin.default_database_name={self.db_name}/d' " \
                  f"{self.conf_path}"
        logger.info(sed_cmd)
        res = self.com.get_sh_result(self.pri_node, sed_cmd)
        logger.info(res)
        msg = self.comsh.restart_db_cluster()
        logger.info(msg)
        status = self.comsh.get_db_cluster_status()
        logger.info(status)
        self.assertTrue("Degraded" in status or "Normal" in status)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
